﻿
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms
Imports Microsoft.Office.Interop





Public Class Form4

    Private dataAdapter As New SqlClient.SqlDataAdapter()
    Private bindingSource1 As New BindingSource()

    Private Sub Form4_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        My.Settings.Form4Size = Me.Size
        My.Settings.Form4Pos = Me.Location
    End Sub

    Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Size = My.Settings.Form4Size
        FormPos(My.Settings.Form4Pos)
        Me.Location = My.Settings.Form4Pos


        ' Bind the DataGridView to the BindingSource
        ' and load the data from the database.
        Me.DataGridView1.DataSource = Me.bindingSource1
    End Sub

    Function GetData(ByVal selectCommand As String)

        Try

            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            cmd.CommandText = selectCommand
            cmd.Connection = sqlConnection1

            Me.dataAdapter = New SqlClient.SqlDataAdapter(cmd)
            Dim commandBuilder As New SqlClient.SqlCommandBuilder(Me.dataAdapter)

            Dim table As New DataTable()
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapter.Fill(table)
            Me.bindingSource1.DataSource = table



            ' Resize the DataGridView columns to fit the newly loaded content.
            Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
        Catch ex As OleDbException

        End Try
        GetData = ""

    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        GetData("select ETEIL.Artikelnummer, ETEIL.Seriennummer, ETEIL.Lagerort, SUM(cast(ETEIL.Transaktionsmenge as int) ) as Menge , ETEIL.Transaktionseinheit " & _
                "from ((dbo.TUER TUER INNER JOIN dbo.ETEIL ETEIL ON TUER.ID = ETEIL.IDm) " & _
                "INNER JOIN dbo.AUF AUF ON AUF.ID = TUER.IDm) " & _
                "where auftragsnummer = '" & TextBox1.Text.ToString & "' and direction = 'R'" & _
                "Group By AUF.Auftragsnummer, ETEIL.Artikelnummer, ETEIL.Seriennummer, ETEIL.Lagerort, ETEIL.Transaktionseinheit ")
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Try
            Dim xls_Appl As Excel.Application               ' Excel Anwendung
            Dim xls_Mappe As Excel.Workbook                 ' Excel Arbeitsmappe
            Dim xls_Blatt As Excel.Worksheet                ' Excel Blatt (Tabelle)
            Dim Anzahlzeilen As Integer
            xls_Appl = New Excel.Application() ' Excel Instanz bilden
            xls_Appl.Visible = True
            xls_Mappe = xls_Appl.Workbooks.Add()
            xls_Blatt = xls_Mappe.ActiveSheet

                With DataGridView1
                    For Spalte As Integer = 0 To .Columns.Count - 1
                        xls_Blatt.Cells(1, Spalte + 1).Value = .Columns(Spalte).HeaderText
                        xls_Blatt.Cells(1, Spalte + 1).Font.Bold = True
                    Next
                    Anzahlzeilen = .Rows.Count
                    If .AllowUserToAddRows = True Then
                        Anzahlzeilen = Anzahlzeilen - 1       ' Leerzeile abziehen
                    End If
                    For Zeile As Integer = 0 To Anzahlzeilen - 1
                        For Spalte As Integer = 0 To .Columns.Count - 1
                            xls_Blatt.Cells(Zeile + 2, Spalte + 1).Value() = .Rows(Zeile).Cells(Spalte).Value.ToString
                        Next
                    Next
                End With
 
        Catch ex As Exception
        End Try

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        savegriddatainfile("C:\Inventur.txt")

    End Sub

    Public Sub SaveGridDataInFile(ByRef fName As String)
        Dim I As Integer = 0
        Dim j As Integer = 0
        Dim cellvalue$
        Dim rowLine As String = ""
        Dim count As Integer

        count = DataGridView1.Columns.Count - 1
        Try
            Dim objWriter As New System.IO.StreamWriter(fName, True)

            For n As Integer = 0 To count
                objWriter.Write(DataGridView1.Columns(n).Name)
                If (n < count) Then
                    objWriter.Write(";")
                End If
            Next
            objWriter.WriteLine(";")

            For j = 0 To (DataGridView1.Rows.Count - 1)
                For I = 0 To (DataGridView1.Columns.Count - 1)
                    If Not TypeOf DataGridView1.CurrentRow.Cells.Item(I).Value Is DBNull Then
                        cellvalue = DataGridView1.Item(I, j).Value
                    Else
                        cellvalue = ""
                    End If
                    rowLine = rowLine + cellvalue + ";"
                Next
                objWriter.WriteLine(rowLine)
                rowLine = ""
            Next
            objWriter.Close()
            MsgBox("Text written to file")
        Catch e As Exception
            MessageBox.Show("Error occured while writing to the file." + e.ToString())
        Finally
            FileClose(1)
        End Try

    End Sub

End Class